from urllib.parse import urlparse, parse_qs

import requests
from flask_apscheduler import scheduler
from sqlalchemy import func

from applications.extensions import db
from applications.models import JyxtNotice, SptLand, SptNotice, JyxtLand
from applications.models.xngx import spt_land_url, jyxt_land_url, spt_notice_url, jyxt_notice_url


# 发送邮件的函数
# @app.route('/send_email')
# def send_email_route():
#     subject = "测试"
#     sender = "ding_0331@163.com"
#     recipients = ["256012956@qq.com"]
#     text_body = "测试"
#     html_body = "<h1>测试</h1>"
#     send_email(subject, sender, recipients, text_body, html_body)
#     return "Email sent!"


# 每半小时执行一次  seconds:多少秒执行一次，minutes：多少分钟执行一次
# 检测出让公告信息
@scheduler.task('interval', id='check_task_gg_info', minutes=30, misfire_grace_time=900)
def check_task_gg_info():
        tb_jyxt_notice()
        tb_spt_notice()
        tb_check_notice()


# 检测成交公示信息
@scheduler.task('interval', id='check_task_land_info', minutes=40, misfire_grace_time=900)
def check_task_land_info():
        tb_jyxt_land()  # 同步交易系统成交宗地信息
        tb_spt_land()  # 同步跟湖北省公共资源交易平台成交宗地尽心
        tb_check_land()  # 进行数据比对并发送邮件


def tb_check_notice():
    notice_list = JyxtNotice.query.filter(JyxtNotice.sfts == 0)
    for d in notice_list:
        notice = db.session.query(SptNotice.GGID, func.count(SptNotice.announcementGuid).label("ZYS")).group_by(
            SptNotice.GGID).filter(SptNotice.GGID == d.GGID).first()
        if not notice:
            subject = f"出让公告推送失败！没有数据!失败!{d.GGBH},公告ID为：{d.GGID}"
            text_body = f"失败!{d.GGBH},在省公共资源交易平台未发现该公告信息"
            html_body = f"失败!{d.GGBH},在省公共资源交易平台未发现该公告信息"
            # send_email(subject, sender, recipients, text_body, html_body)
        elif int(d.ZYS) != notice[1]:
            subject = f"出让公告推送失败！数据不一致！失败!{d.GGBH},公告ID为：{d.GGID}"
            text_body = f"失败!省公共资源交易平台，{d['GGBH']}公告,资源数据为{notice[1]}条，但是交易系统为{d.ZYS}宗"
            html_body = f"失败!省公共资源交易平台，{d['GGBH']}公告,资源数据为{notice[1]}条，但是交易系统为{d.ZYS}宗"
            # send_email(subject, sender, recipients, text_body, html_body)
        else:
            subject = f"出让公告推送成功!成功!{d.GGBH},公告ID为：{d.GGID}"
            text_body = f"成功!省公共资源交易平台，{d.GGBH}公告,资源数据为{notice[1]}条，交易系统为{d.ZYS}宗"
            html_body = f"成功!省公共资源交易平台，{d.GGBH}公告,资源数据为{notice[1]}条，交易系统为{d.ZYS}宗"
            JyxtNotice.query.filter(JyxtNotice.GGID == d.GGID).update({'sfts': 1})
            db.session.commit()
            # send_email(subject, sender, recipients, text_body, html_body)









def tb_check_land():
    land_list = JyxtLand.query.filter(JyxtLand.sfts == 0)
    for d in land_list:
        land = JyxtLand.query.filter(JyxtLand.ZYID == d.ZYID).first()
        if not land:
            subject = f"宗地信息同步检查失败！没有数据!失败!{d.XZQMC},资源编号为:{d.ZYBH}"
            text_body = f"失败!{d.XZQMC}的{d.ZYBH},在省公共资源交易平台未发现该宗地成交信息,"
            html_body = f"失败!{d.XZQMC}的{d.ZYBH},在省公共资源交易平台未发现该宗地成交信息,资源ID为{d.ZYID}"
            # send_email(subject, sender, recipients, text_body, html_body)
        else:
            subject = f"宗地推送信息检查成功!成功!{d.XZQMC},资源编号为：{d.ZYBH}"
            text_body = f"成功!省公共资源交易平台，{d.XZQMC},资源编号为：{d.ZYBH}"
            html_body = f"成功!省公共资源交易平台，{d.XZQMC},资源编号为：{d.ZYBH}"
            JyxtLand.query.filter(JyxtLand.ZYID == d.ZYID).update({'sfts': 1})
            db.session.commit()
            # send_email(subject, sender, recipients, text_body, html_body)





def tb_jyxt_land():  # 获取交易系统公共信息
    rows = requests.post(jyxt_land_url).json()['rows']
    for row in rows:
        notice = JyxtLand.query.filter(JyxtLand.ZYID == row['ZYID']).first()
        if not notice:
            db.session.add(convert_jyxt_land(row))
            db.session.commit()




def tb_spt_land():  # 获取省平台成交宗地信息
    rows = requests.post(spt_land_url).json()['data']
    for row in rows:
        land = SptLand.query.filter(SptLand.dealLandCode == row['dealLandCode']).first()
        if not land:
            db.session.add(convert_spt_land(row))
            db.session.commit()





def tb_jyxt_notice():  # 更新交易系统交易公告信息
    res = requests.post(jyxt_notice_url).json()
    rows = res['rows']
    for row in rows:
        notice = JyxtNotice.query.filter(JyxtNotice.GGID == row['GGID']).first()
        if not notice:
            db.session.add(convert_jyxt_notice(row))
            db.session.commit()





def tb_spt_notice():
    rows = requests.post(spt_notice_url).json()['data']
    for row in rows:
        notice = SptNotice.query.filter(SptNotice.announcementGuid == row['announcementGuid']).first()
        if not notice:
            db.session.add(convert_spt_notice(row))
            db.session.commit()


@app.route('/update_spt_notice')
def query_spt_notice():  # 同步省公共资源交易平台数据公告信息
    tb_spt_notice()
    return '省平台出让公告信息同步成功'




def convert_jyxt_land(row):
    land = JyxtLand()
    land.ZYID = row['ZYID']
    land.JDDW = row['JDDW']
    land.XZQMC = row['XZQMC']
    land.ZYBH = row['ZYBH']
    land.ZYWZ = row['ZYWZ']
    return land


def convert_spt_land(row):
    parsed_url = urlparse(row['landUrl'])
    query_params = parsed_url.query
    params = parse_qs(query_params)
    land = SptLand()
    land.dealLandCode = row['dealLandCode']
    land.unifiedDealCode = row['unifiedDealCode']
    land.landCode = row['landCode']
    land.landUsage = row['landUsage']
    land.landPosition = row['landPosition']
    land.lendingName = row['lendingName']
    land.dataTimeStamp = row['dataTimeStamp'],
    land.dealAnnouncementGuid = row['dealAnnouncementGuid']
    land.landProjectName = row['landProjectName']
    land.landUrl = row['landUrl']
    land.publishingTime = row['publishingTime']
    land.ZYID = params.get('id', [])[0]
    return land


def convert_jyxt_notice(row):
    notice = JyxtNotice()
    notice.ZYLB = row['ZYLB']
    notice.XZQBM = row['XZQBM']
    notice.GGBH = row['GGBH']
    notice.GGLB = row['GGLB']
    notice.GGFBSJ = row['GGFBSJ']
    notice.GGID = row['GGID']
    notice.ZYS = row['ZYS']
    notice.JYFS = row['JYFS']
    notice.GGMC = row['GGMC']
    notice.FBR = row['FBR']
    notice.JYLB = row['JYLB']
    notice.GSFJ = row['GSFJ']
    notice.XZQMC = row['XZQMC']
    notice.TZFJ = row['TZFJ']
    return notice


def convert_spt_notice(row):
    notice = SptNotice()
    parsed_url = urlparse(row['url'])
    query_params = parsed_url.query
    params = parse_qs(query_params)
    notice.announcementGuid = row['announcementGuid']
    notice.isDeleted = row['isDeleted']
    notice.unifiedDealCode = row['unifiedDealCode']
    notice.announcementCode = row['announcementCode']
    notice.announcementTitle = row['announcementTitle']
    notice.announcementType = row['announcementType']
    notice.publishingTime = row['publishingTime']
    notice.url = row['url']
    notice.endDate = row['endDate']
    notice.announcementStartTime = row['announcementStartTime']
    notice.listingStartTime = row['listingStartTime']
    notice.listingDeadline = row['listingDeadline']
    notice.listingType = row['listingType']
    notice.supplyType = row['supplyType']
    notice.fieldNum = row['fieldNum']
    notice.landDistrict = row['landDistrict']
    notice.announcementUnit = row['announcementUnit']
    notice.liaisonUnit = row['liaisonUnit']
    notice.unitAddress = row['unitAddress']
    notice.zipCode = row['zipCode']
    notice.contactPerson = row['contactPerson']
    notice.contactNumber = row['contactNumber']
    notice.email = row['email']
    notice.createTime = row['createTime']
    notice.changeTime = row['changeTime']
    notice.retreatTime = row['retreatTime']
    notice.attachmentSetCode = row['attachmentSetCode']
    notice.platformCode = row['platformCode']
    notice.pubServicePlatCode = row['pubServicePlatCode']
    notice.dataTimestamp = row['dataTimestamp']
    notice.uploadGJStatus = row['uploadGJStatus']
    notice.announcementDeadline = row['announcementDeadline']
    notice.jyptId = row['jyptId']
    notice.cancelReason = row['cancelReason']
    notice.GGID = params.get('id', [])[0]
    return notice
